package com.example.mybatis.mapper;

import com.example.mybatis.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface UserInfoMapper {
    @ResultMap("resultMap")
    @Select("select * from userinfo")
    List<UserInfo> getUserInfoAll();

    @Select("select * from userinfo where delete_flag = #{deleteFlag}")
    List<UserInfo> getUserInfoByDeleteFlag(Integer deleteFlag);

    @Select("select * from userinfo where delete_flag = ${deleteFlag}")
    List<UserInfo> getUserInfoByDeleteFlag2(Integer deleteFlag);

    @Select("select * from userinfo where delete_flag = #{deleteFlag} and gender = #{gender}")
    List<UserInfo> getUserInfo2(@Param("deleteFlag") Integer deleteFlag, Integer gender);

    @Select("select * from userinfo where username = #{userName}")
    UserInfo getUserByName(String userName);

    @Select("select * from userinfo where username = '${userName}'")
    UserInfo getUserByName2(String userName);

    @Select("select * from userinfo where id = #{id}")
    UserInfo getUserInfo3(Integer id);

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into userinfo (username, password, age, gender) values (#{userName},#{password},#{age},#{gender})")
    Integer insert(UserInfo userInfo);

    @Insert("insert into userinfo (username, password, age, gender) " +
            "values (#{userInfo.userName},#{userInfo.password},#{userInfo.age},#{userInfo.gender})")
    Integer insert2(@Param("userInfo") UserInfo userInfo);

    @Delete("delete from userinfo where id = #{id}")
    Integer delete(Integer id);

    @Update("update userinfo set password = #{password}, age = #{age}, gender = #{gender} where id = #{id}")
    Integer update(UserInfo userInfo);

    //起别名
    @Select("select id, username, password, age, gender, phone," +
            " delete_flag as deleteFlag, create_time as " +
            "createTime, update_time as updateTime from userinfo")
    List<UserInfo> selectUserInfos ();

    //结果映射
    @Results(id = "resultMap" ,value = {
            @Result(column = "delete_flag", property = "deleteFlag"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    @Select("select * from userinfo")
    List<UserInfo> selectUserInfos2 ();

    @Select("select * from userinfo")
    List<UserInfo> selectUserInfos3 ();

    //演示SQL注入
    @Select("select * from userinfo where username = #{username}")
    UserInfo selectUserByName(String username);

    @Select("select * from userinfo where username = '${username}'")
    UserInfo selectUserByName2(String username);

    @Select("select * from userinfo where username = '${username}' and password = '${password}'")
    UserInfo queryUserByNameAndPassword(String username, String password);

    @Select("select * from userinfo order by id ${order}")
    List<UserInfo> queryUserListByOrder(String order);

    @Select("select * from userinfo where username like '%${username}%'")
    List<UserInfo> queryUserListByLikes(String username);

    @Insert("<script>" +
            "insert into userinfo" +
            "<trim prefix='(' suffix=')' suffixOverrides=','> " +
            "            <if test='username != null'>username,</if> " +
            "            <if test='password != null'>password,</if> " +
            "            <if test='age != null'>age,</if> " +
            "            <if test='gender != null'>gender</if> " +
            "        </trim> " +
            "        values " +
            "         <trim prefix='(' suffix=')' suffixOverrides=','> " +
            "             <if test='username != null'>#{username},</if> " +
            "             <if test='password != null'>#{password},</if> " +
            "             <if test='age != null'>#{age},</if> " +
            "             <if test='gender != null'>#{gender}</if> " +
            "         </trim>" +
            "</script>")
    Integer insertByCondtion(UserInfo userInfo);
}
